﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using WpfTool.Models;

namespace WpfTool.Db
{
    /// <summary>
    /// BiaoCaoZuo.xaml 的交互逻辑
    /// </summary>
    public partial class BiaoCaoZuo : UserControl
    {
        List<BiaoModel> list = new List<BiaoModel>();
        public BiaoCaoZuo()
        {
            InitializeComponent();
            BiaoList();
        }

        public void BiaoList()
        {
            string sql = @"select relname BiaoMing,concat(relname,'(',obj_description(relfilenode,'pg_class'),')') as ZhuShi
                            from pg_class
                            WHERE relnamespace=2200 and relam=2";

            sql = @"SELECT t.Name BiaoMing,t.Name +'('+ CAST(ISNULL(p.VALUE, '') AS nvarchar(500))+')' ZhuShi
                    FROM sys.tables AS t
                    LEFT JOIN sys.extended_properties AS p
                        ON t.object_id = p.major_id
                            AND p.minor_id = 0";
            list = DbHelper.DbMssql.Ado.SqlQuery<BiaoModel>(sql);
            CbBiao.ItemsSource = list;
            CbBiao.DisplayMemberPath = "ZhuShi";
            CbBiao.SelectedValuePath = "BiaoMing";
            CbBiao.SelectedIndex = 0;
        }

        private void CbBiao_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            ComboBox box = (ComboBox)sender;
            string text = box.SelectedValue.ToString();


            string sql = @"SELECT COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,
                            CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE
                            FROM information_schema.COLUMNS
                            WHERE TABLE_NAME='xt_user'
                            ORDER BY TABLE_NAME, ORDINAL_POSITION";

            sql = @"SELECT a.colorder XuHao, a.name LieMing, 
                        case when COLUMNPROPERTY(a.id, a.name, 'IsIdentity')= 1 then 1 else 0 end ZiZheng, 
	                    CASE WHEN c.colid is not null THEN 1 ELSE 0 END ZhuJian,
                      b.name LeiXing, COLUMNPROPERTY(a.id, a.name, 'PRECISION') ChangDu, 
                      isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'),0) XiaoShu, 
                      a.isnullable ShiKong, isnull(e.text, '') MoRenZhi, 
                      isnull(g.[value], '') ZhuShi
                    FROM syscolumns a 
                      left join systypes b on a.xusertype = b.xusertype 
	                    LEFT JOIN sysindexkeys c on c.id=a.id and c.colid = a.colid
                      inner join sysobjects d on a.id = d.id and d.xtype = 'U' and d.name<>'dtproperties' 
                      left join syscomments e on a.cdefault = e.id 
                      left join sys.extended_properties g on a.id = g.major_id and a.colid = g.minor_id
                    where d.name = '" + text + @"'
                    order by a.id, a.colorder";

//            sql = @"SELECT 
//    o.name TableName,
//    c.name ColumnName,
//    t.Name DataType,
//    c.max_length MaxLength,
//    c.precision Precision,
//    c.scale Scale,
//    c.is_nullable IsNull,
//    ISNULL(i.is_primary_key, 0) 'PrimaryKey',
//    ISNULL(i.is_unique_constraint, 0) 'UniqueKey',
//    ISNULL(i.name, 0) 'IndexName'
//FROM  sys.objects o
//INNER JOIN sys.columns c ON o.object_id = c.object_id
//INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
//LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
//LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
//WHERE o.name = 'MEDICAL_EXCheck'";
            var list = DbHelper.DbMssql.Ado.SqlQuery<LieModel>(sql);
            dgList.ItemsSource = list;
        }


        private void EditClick(object sender, RoutedEventArgs e)
        {

        }

        private void DeleteClick(object sender, RoutedEventArgs e)
        {

        }
    }

    public class BiaoModel
    {
        public string BiaoMing { get; set; }
        public string ZhuShi { get; set; }
    }

    public class LieModel
    {
        public int XuHao { get; set; }
        public string LieMing { get; set; }
        public string LeiXing { get; set; }
        public string ZhuShi { get; set; }
    }
}
